package cn.bitkit.assist.jdbc;

import cn.bitkit.tools.data.BeanUtil;
import cn.bitkit.tools.common.IOUtil;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;

import java.sql.*;
import java.util.*;

/**
 *
 * @author changbo
 * 
 */
@Slf4j
public class JdbcHelper {

	public static List<Map<String,Object>> query(String sql){
		return query(sql, new ArrayList<>());
	}

	public static List<Map<String,Object>> query(String sql, Object... params){
		return query(sql, Arrays.asList(params));
	}

	public static List<Map<String,Object>> query(String sql, List<Object> params){
		sql = processSql(sql);
		Connection conn = JdbcConnection.getConnection();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			setParameter(ps, params);
			ResultSet rs = ps.executeQuery();
			List<Map<String,Object>> list = new ArrayList<>();
			while(rs.next()){
				list.add(getResultMap(rs));
			}
			return list;
		} catch (SQLException e) {
			log.error("", e);
		} finally{
			IOUtil.close(ps);
			IOUtil.close(conn);
		}
		return null;
	}

	public static <T> List<T> query(String sql, Class<T> clazz){
		return query(sql, new ArrayList<>(), clazz);
	}

	public static <T> List<T> query(String sql, List<Object> params, Class<T> clazz){
		List<Map<String, Object>> mapList = query(sql, params);
		return BeanUtil.convertList(mapList, clazz);
	}

	public static boolean executeBatch(String sql, List<List<Object>> list){
		sql = processSql(sql);
		Connection conn = JdbcConnection.getConnection();
		PreparedStatement ps = null;
		try {
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(sql);
			for(int i=0; list!=null && i<list.size(); i++){
				setParameter(ps, list.get(i));
				ps.addBatch();
			}
			ps.executeBatch();
			commit(conn);
			return true;
		} catch (SQLException e) {
			rollback(conn);
			log.error("", e);
		} finally{
			IOUtil.close(ps);
			IOUtil.close(conn);
		}
		return false;
	}

	public static boolean execute(String sql){
		return executeUpdate(sql) > 0;
	}

	public static boolean execute(String sql, Object... params){
		return execute(sql, Arrays.asList(params));
	}

	public static boolean execute(String sql, List<Object> params){
		sql = processSql(sql);
		Connection conn = JdbcConnection.getConnection();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			setParameter(ps, params);
			ps.execute();
			return true;
		} catch (SQLException e) {
			rollback(conn);
			log.error("", e);
		} finally{
			IOUtil.close(ps);
			IOUtil.close(conn);
		}
		return false;
	}

	public static int executeUpdate(String sql){
		Connection conn = JdbcConnection.getConnection();
		Statement state = null;
		try {
			state = conn.createStatement();
			return state.executeUpdate(sql);
		} catch (SQLException e) {
			rollback(conn);
			log.error("", e);
		} finally{
			IOUtil.close(state);
			IOUtil.close(conn);
		}
		return 0;
	}

	/**
	 * {CALL demoSp(? , ?)}
	 */
	@SneakyThrows
    public static void call(String sql, CallValue callValue){
		sql = processSql(sql);
		Connection conn = JdbcConnection.getConnection();
		CallableStatement cs = null;
		try {
			cs = conn.prepareCall(sql);
			if(callValue != null) {
                for (Map.Entry<Integer, Object> entry : callValue.getInMap().entrySet()){
                    cs.setObject(entry.getKey(), entry.getValue());
                }
                for (Map.Entry<Integer, Integer> entry : callValue.getOutTypeMap().entrySet()){
                    cs.registerOutParameter(entry.getKey(), entry.getValue());
                }
			}
			cs.execute();
			if(callValue != null) {
                for (Map.Entry<Integer, Integer> entry : callValue.getOutTypeMap().entrySet()){
                    callValue.out(entry.getKey(), cs.getObject(entry.getKey()));
                }
			}
		} catch (SQLException e) {
			rollback(conn);
			log.error("", e);
		} finally{
			IOUtil.close(cs);
			IOUtil.close(conn);
		}
	}

	public static void commit(Connection conn){
		try {
			if(!conn.getAutoCommit()){
				conn.commit();
			}
		} catch (SQLException e) {
			log.error("", e);
		}
	}

	public static void rollback(Connection conn){
		try {
			if(!conn.getAutoCommit()){
				conn.rollback();
			}
		} catch (SQLException e) {
			log.error("", e);
		}
	}

	public static void setParameter(PreparedStatement ps, List<Object> params) throws SQLException{
		int i = 1;
		for(Object obj : params){
			ps.setObject(i++, obj);
		}
	}

	public static String processSql(String sql) {
		if(sql == null) {
			return null;
		}
		sql = sql.trim();
		if(sql.endsWith(";")) {
			sql = sql.substring(0, sql.length()-1);
		}
		return sql;
	}

	public static Map<String, Object> getResultMap(ResultSet rs) throws SQLException{
		Map<String, Object> tempData = new LinkedHashMap<>();
		ResultSetMetaData rsm = rs.getMetaData();
		for(int j=1; j<=rsm.getColumnCount(); j++){
			Object obj = null;
			try{
				obj = rs.getObject(j);
			}catch(Exception e){
				try{
					obj = rs.getTimestamp(j);
				}catch (Exception e2) {
					log.error("", e2);
				}
			}
			tempData.put(rsm.getColumnLabel(j), obj);
		}
		return tempData;
	}

}
